﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;
using System.Data;
using Dapper;
using Earthwatchers.Models;

namespace Earthwatchers.Data
{
    public class LandRepository : ILandRepository
    {
        private readonly IDbConnection connection;

        public LandRepository(string connectionString)
        {
            connection = new SqlConnection(connectionString);
        }

        public void CreateLand(List<Land> newLand)
        {
            var nrland = newLand.Count.ToString();
            Debug.WriteLine("Start creating" + nrland + " land.");
            connection.Open();
            var i = 0;
            foreach(var land in newLand)
            {
                var sql = String.Format(@"insert into Earthwatchers.Land(Centroid, Geohexkey, LandThreat, Distance, LandStatus, LandType)
                    values (geography::STGeomFromText('POINT ({0} {1})', 4326), @geohexkey, 0, 0, 1, -1)", land.Longitude, land.Latitude);
                var command = connection.CreateCommand();
                command.CommandText = sql;
                command.Parameters.Add(new SqlParameter("@geohexkey", land.GeohexKey));
                command.ExecuteNonQuery();
                i++;
                Debug.WriteLine(i.ToString() + " of " + nrland.ToString());
            }
            connection.Close();
        }

        public Land GetLand(int id)
        {
            connection.Open();
            var lands = connection.Query<Land>("select l.Id, Centroid.Lat as Latitude, Centroid.Long as Longitude, GeohexKey, Distance, LandThreat, LandStatus, StatusChangedDateTime, e.Id as EarthwatcherId from Land l left join Earthwatcher e on l.EarthwatcherGuid=e.EarthwatcherGuid where l.Id=@Id", new { Id = id });
            connection.Close();
            var land = lands.FirstOrDefault();
            AddConfirmersAndDeconfirmers(land);
            if (land != null)
            {
                land.StatusChangedDateTime = DateTime.Now;
            }
            return land;
        }

        public Land GetLandByEarthwatcherName(string name)
        {
            connection.Open();
            var lands = connection.Query<Land>("select l.Id, Centroid.Lat as Latitude, Centroid.Long as Longitude, GeohexKey, Distance, LandThreat, LandStatus, StatusChangedDateTime, e.Id as EarthwatcherId from Land l left join Earthwatcher e on l.EarthwatcherGuid=e.EarthwatcherGuid where e.Name=@Name", new { Name = name });
            connection.Close();
            var land = lands.FirstOrDefault();
            return land;
        }

        public Land PostLand(Land land)
        {
            throw new NotImplementedException();
        }

        public List<Land> GetAll()
        {
            connection.Open();
            var l = connection.Query<Land>("select top 100 l.Id, Centroid.Lat as Latitude, Centroid.Long as Longitude, GeohexKey, Distance, LandThreat, LandStatus, StatusChangedDateTime, e.Id as EarthwatcherId from Land l left join Earthwatcher e on l.EarthwatcherGuid=e.EarthwatcherGuid");
            var lands=l.ToList();
            connection.Close();
            AddConfirmersAndDeconfirmers(lands);
            return lands;
        }

        public List<Land> GetLandByIntersect(string wkt)
        {
            connection.Open();
            string sql = String.Format(@"select top 100 l.Id, Centroid.Lat as Latitude, Centroid.Long as Longitude, GeohexKey, Distance, LandThreat, LandStatus, StatusChangedDateTime, e.Id as EarthwatcherId from Land l left join Earthwatcher e on l.EarthwatcherGuid=e.EarthwatcherGuid
             where LandStatus > 1 AND centroid.STIntersects(geography::STGeomFromText('{0}', 4326))=1 ", wkt);
            var lands = connection.Query<Land>(sql).ToList();
            connection.Close();
            AddConfirmersAndDeconfirmers(lands);            
            return lands;
        }

        public List<Land> GetLandByStatus(LandStatus status)
        {
            connection.Open();
            var sql = String.Format(@"select top 100 l.Id, Centroid.Lat as Latitude, Centroid.Long as Longitude, GeohexKey, Distance, LandThreat, LandStatus, StatusChangedDateTime, e.Id as EarthwatcherId from Land l left join Earthwatcher e on l.EarthwatcherGuid=e.EarthwatcherGuid
             where LandStatus = {0}", (int)status);
            var lands = connection.Query<Land>(sql).ToList();
            connection.Close();
            AddConfirmersAndDeconfirmers(lands);            
            return lands;
        }

        public void UpdateLandStatus(int id, LandStatus landStatus)
        {
            connection.Open();
            const string sql = "update land set LandStatus=@LandStatus, StatusChangedDateTime=@StatusChangedDateTime where Id=@Id";
            var command=connection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@StatusChangedDateTime", DateTime.Now.ToUniversalTime()));
            command.Parameters.Add(new SqlParameter("@LandStatus", landStatus));
            command.Parameters.Add(new SqlParameter("@Id", id));
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void UpdateLandEarthwatcherguid(string geohex, string earthwatcherguid)
        {
            connection.Open();
            const string sql = "update Earthwatchers.Land set EarthwatcherGuid = @EarthwatcherGuid where GeohexKey = @GeohexKey";
            var command = connection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@EarthwatcherGuid", earthwatcherguid));
            command.Parameters.Add(new SqlParameter("@GeohexKey", geohex));
            command.ExecuteNonQuery();
            connection.Close();
        }




        public void UnassignLand(int id)
        {
            connection.Open();
            const string sql = "update land set LandStatus=1, EarthwatcherGuid=null where Id=@Id";
            var command = connection.CreateCommand();
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@Id", id));
            command.ExecuteNonQuery();
            connection.Close();
        }

        public IEnumerable<int> GetConfirmers(int id)
        {
            connection.Open();
            var sql = String.Format(@"select Earthwatcher from DeforestationConfirmers where land={0}",id);
            var confirmers = connection.Query<int>(sql);
            connection.Close();
            return confirmers;
        }

        public IEnumerable<int> GetDeconfirmers(int id)
        {
            connection.Open();
            var sql = String.Format(@"select Earthwatcher from DeforestationDeconfirmers where land={0}", id);
            var confirmers = connection.Query<int>(sql);
            connection.Close();
            return confirmers;
        }

        public void DeleteConfirmer(int id, Earthwatcher earthwatcher)
        {
            connection.Open();
            var sql = String.Format("delete from DeforestationConfirmers where land={0} and Earthwatcher={1}", id, earthwatcher.Id);
            connection.Execute(sql);
            connection.Close();
        }

        public void DeleteDeconfirmer(int id, Earthwatcher earthwatcher)
        {
            connection.Open();
            var sql = String.Format("delete from DeforestationDeconfirmers where land={0} and Earthwatcher={1}", id, earthwatcher.Id);
            connection.Execute(sql);
            connection.Close();
        }

        public void InsertConfirmer(int id, Earthwatcher earthwatcher)
        {
            connection.Open();
            var sql = String.Format("insert into DeforestationConfirmers(Land,Earthwatcher) values ({0},{1})", id, earthwatcher.Id);
            connection.Execute(sql);
            connection.Close();
        }

        public void InsertDeconfirmer(int id, Earthwatcher earthwatcher)
        {
            connection.Open();
            var sql = String.Format("insert into DeforestationDeconfirmers(Land,Earthwatcher) values ({0},{1})", id, earthwatcher.Id);
            connection.Execute(sql);
            connection.Close();
        }


        public void ConfirmLand(int id, Earthwatcher earthwatcher)
        {
            var confirmers = GetConfirmers(id);
            if (!confirmers.Contains(earthwatcher.Id))
            {
                InsertConfirmer(id, earthwatcher);

                var deconfirmers = GetDeconfirmers(id);

                if (deconfirmers.Contains(earthwatcher.Id))
                {
                    DeleteDeconfirmer(id, earthwatcher);
                }
            }
            // possible todo: give user warning that land is already confirmed
        }

        public void DeconfirmLand(int id, Earthwatcher earthwatcher)
        {
            var deconfirmers = GetDeconfirmers(id);
            if (!deconfirmers.Contains(earthwatcher.Id))
            {
                InsertDeconfirmer(id, earthwatcher);

                var confirmers = GetConfirmers(id);

                if (confirmers.Contains(earthwatcher.Id))
                {
                    DeleteConfirmer(id, earthwatcher);
                }
            }
            // possible todo: give user warning that land is already deconfirmed
        }

        private void AddConfirmersAndDeconfirmers(IEnumerable<Land> lands)
        {
            foreach (var land in lands)
            {
                AddConfirmersAndDeconfirmers(land);
            }
        }

        private void AddConfirmersAndDeconfirmers(Land land)
        {
            land.DeforestationConfirmers =String.Join(",", GetConfirmers(land.Id));
            land.DeforestationDeconfirmers = String.Join(",", GetDeconfirmers(land.Id));
        }
    }
}
